﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;

namespace MyWebApplication
{
    public class PagerUtil
    {

        /// <summary>
        /// Sql 分页
        /// </summary>
        /// <param name="sql">Sql</param>
        /// <param name="searchModel">检索实体</param>
        /// <param name="resultModel">查询结果实体</param>
        /// <returns></returns>
        public static void CurrentPageData(string sql, SearchModel searchModel, GridResultModel<DataTable> resultModel)
        {
            var countParams = new SqlParameter[searchModel.Conditions.Count];
            //查询总记录数
            StringBuilder countSql = new StringBuilder();
            countSql.Append("SELECT COUNT(1) FROM (" + sql + ") t WHERE 1=1 ");

            //获取查询条件
            StringBuilder countWhereSql = GetSqlWhere(searchModel, countParams);
            if (countWhereSql.Length > 0)
            {
                countSql.Append(countWhereSql);
            }
            object countResult = SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, countSql.ToString(), countParams);
            if (countResult != null)
            {
                int.TryParse(countResult.ToString(), out resultModel.Total);
            }

            if (resultModel.Total > 0)
            {//当总记录数大于0时，查询分页结果
                StringBuilder currentPagerSql = new StringBuilder();
                currentPagerSql.Append(" SELECT TOP (" + searchModel.Records + ") * FROM ");
                currentPagerSql.Append(" (SELECT ROW_NUMBER()OVER(ORDER BY " + searchModel.OrderBy + " " + searchModel.Sord + ") RowNum,* FROM (" + sql + ") t1 WHERE 1=1 {0}) t2 ");
                currentPagerSql.Append(" WHERE RowNum>(" + ((searchModel.PageIndex - 1) * searchModel.Records) + ") ");
                SqlParameter[] currentPagerParams = new SqlParameter[searchModel.Conditions.Count];

                //获取查询条件
                StringBuilder currentPagerWhereSql = GetSqlWhere(searchModel, currentPagerParams);
                string currentPagerSqlStr = currentPagerSql.ToString();
                currentPagerSqlStr = string.Format(currentPagerSqlStr, currentPagerWhereSql);
                DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionString, CommandType.Text, currentPagerSqlStr, currentPagerParams);
                if (ds != null && ds.Tables.Count > 0)
                {
                    resultModel.Rows = ds.Tables[0];
                }

                //计算总页数
                if (!0.Equals(searchModel.Records))
                {
                    resultModel.Records = (int)Math.Ceiling((double)resultModel.Total / searchModel.Records);
                }
            }
        }

        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="sqlParams"></param>
        private static StringBuilder GetSqlWhere(SearchModel searchModel, SqlParameter[] sqlParams)
        {
            int itemIndex = 0;
            StringBuilder whereSql = new StringBuilder();
            foreach (KeyValuePair<string, object> item in searchModel.Conditions)
            {
                whereSql.Append(" AND " + item.Key + " LIKE @" + item.Key);
                sqlParams[itemIndex] = new SqlParameter("@" + item.Key, "%" + item.Value + "%");
                itemIndex++;
            }
            return whereSql;
        }

        /// <summary>
        /// 获取查询条件
        /// </summary>
        public static SearchModel GetConditions()
        {
            SearchModel searchModel = new SearchModel();
            string[] allKeys = HttpContext.Current.Request.QueryString.AllKeys;
            string value;
            foreach (string key in allKeys)
            {
                value = HttpContext.Current.Request.QueryString[key];
                //非查询条件参数
                if (!SearchModel.NotConditions.Contains(key))
                {
                    searchModel.Conditions[key] = value;
                }

                //每页记录数
                if (SearchModel.KeyRecords.Equals(key))
                {
                    int.TryParse(value, out searchModel.Records);
                }

                //当前页码
                if (SearchModel.KeyPageIndex.Equals(key))
                {
                    int.TryParse(value, out searchModel.PageIndex);
                }

                //排序字段
                if (SearchModel.KeyOrderBy.Equals(key))
                {
                    searchModel.OrderBy = value;
                }

                //排序方式
                if (SearchModel.KeySord.Equals(key))
                {
                    searchModel.Sord = value;
                }
            }
            return searchModel;
        }
    }
}